-- Row Level Security (RLS) Policies for Product Showcase Platform
-- This script creates RLS policies for users and products tables
-- ==================== 辅助函数 ====================
-- 创建获取当前用户ID的函数
-- 这个函数从应用层设置的会话变量中获取当前用户ID
CREATE OR REPLACE FUNCTION current_user_id() RETURNS UUID LANGUAGE sql STABLE SECURITY DEFINER AS $$
SELECT COALESCE(
        current_setting('app.current_user_id', true)::uuid,
        NULL
    );
$$;
-- 创建检查用户是否已认证的函数
CREATE OR REPLACE FUNCTION is_authenticated() RETURNS BOOLEAN LANGUAGE sql STABLE SECURITY DEFINER AS $$
SELECT current_user_id() IS NOT NULL;
$$;
-- 创建检查用户是否为管理员的函数（预留）
CREATE OR REPLACE FUNCTION is_admin() RETURNS BOOLEAN LANGUAGE sql STABLE SECURITY DEFINER AS $$
SELECT EXISTS (
        SELECT 1
        FROM users
        WHERE id = current_user_id()
            AND is_active = true -- 可以添加 role 字段来判断管理员权限
    );
$$;
-- ==================== 用户表 RLS 策略 ====================
-- 启用用户表的行级安全
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- 1. 注册策略：允许任何人插入新用户（注册功能）
CREATE POLICY "users_insert_registration" ON users FOR
INSERT WITH CHECK (true);
-- 2. 查看策略：任何人都可以查看用户的公开信息
-- 注意：敏感字段将通过视图或应用层过滤
CREATE POLICY "users_select_public" ON users FOR
SELECT USING (true);
-- 3. 更新策略：用户只能更新自己的记录
CREATE POLICY "users_update_own" ON users FOR
UPDATE USING (id = current_user_id()) WITH CHECK (id = current_user_id());
-- 4. 删除策略：用户只能删除自己的记录
CREATE POLICY "users_delete_own" ON users FOR DELETE USING (id = current_user_id());
-- ==================== 产品表 RLS 策略 ====================
-- 启用产品表的行级安全
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
-- 1. 查看策略：任何人都可以查看所有产品
CREATE POLICY "products_select_public" ON products FOR
SELECT USING (true);
-- 2. 插入策略：只有认证用户可以创建产品，且只能设置自己为所有者
CREATE POLICY "products_insert_authenticated" ON products FOR
INSERT WITH CHECK (
        is_authenticated()
        AND user_id = current_user_id()
    );
-- 3. 更新策略：用户只能更新自己的产品
CREATE POLICY "products_update_own" ON products FOR
UPDATE USING (
        is_authenticated()
        AND user_id = current_user_id()
    ) WITH CHECK (
        is_authenticated()
        AND user_id = current_user_id()
    );
-- 4. 删除策略：用户只能删除自己的产品
CREATE POLICY "products_delete_own" ON products FOR DELETE USING (
    is_authenticated()
    AND user_id = current_user_id()
);
-- ==================== 安全视图 ====================
-- 创建用户公开信息视图，只包含安全的字段
CREATE OR REPLACE VIEW users_public AS
SELECT id,
    username,
    full_name,
    organization,
    department,
    is_active,
    created_at
FROM users
WHERE is_active = true;
-- 为视图添加注释
COMMENT ON VIEW users_public IS '用户公开信息视图，只包含安全的非敏感字段';
-- ==================== 权限设置 ====================
-- 创建应用角色（如果不存在）
DO $$ BEGIN IF NOT EXISTS (
    SELECT 1
    FROM pg_roles
    WHERE rolname = 'app_user'
) THEN CREATE ROLE app_user;
END IF;
IF NOT EXISTS (
    SELECT 1
    FROM pg_roles
    WHERE rolname = 'app_anonymous'
) THEN CREATE ROLE app_anonymous;
END IF;
END $$;
-- 设置匿名用户权限
GRANT SELECT ON users_public TO app_anonymous;
GRANT SELECT ON products TO app_anonymous;
GRANT INSERT ON users TO app_anonymous;
-- 设置认证用户权限
GRANT SELECT,
    INSERT,
    UPDATE,
    DELETE ON users TO app_user;
GRANT SELECT,
    INSERT,
    UPDATE,
    DELETE ON products TO app_user;
GRANT USAGE ON SEQUENCE users_id_seq TO app_user;
GRANT USAGE ON SEQUENCE products_id_seq TO app_user;
-- ==================== 审计和日志 ====================
-- 创建审计日志表（可选）
CREATE TABLE IF NOT EXISTS audit_log (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    operation VARCHAR(10) NOT NULL,
    -- INSERT, UPDATE, DELETE
    user_id UUID,
    record_id UUID,
    old_values JSONB,
    new_values JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- 创建审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_function() RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN IF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (
        table_name,
        operation,
        user_id,
        record_id,
        old_values
    )
VALUES (
        TG_TABLE_NAME,
        TG_OP,
        current_user_id(),
        OLD.id,
        to_jsonb(OLD)
    );
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (
        table_name,
        operation,
        user_id,
        record_id,
        old_values,
        new_values
    )
VALUES (
        TG_TABLE_NAME,
        TG_OP,
        current_user_id(),
        NEW.id,
        to_jsonb(OLD),
        to_jsonb(NEW)
    );
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (
        table_name,
        operation,
        user_id,
        record_id,
        new_values
    )
VALUES (
        TG_TABLE_NAME,
        TG_OP,
        current_user_id(),
        NEW.id,
        to_jsonb(NEW)
    );
RETURN NEW;
END IF;
RETURN NULL;
END;
$$;
-- 为用户表添加审计触发器
CREATE TRIGGER users_audit_trigger
AFTER
INSERT
    OR
UPDATE
    OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
-- 为产品表添加审计触发器
CREATE TRIGGER products_audit_trigger
AFTER
INSERT
    OR
UPDATE
    OR DELETE ON products FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
-- ==================== 索引优化 ====================
-- 为 RLS 策略相关字段添加索引
CREATE INDEX IF NOT EXISTS idx_products_user_id_active ON products(user_id)
WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_active ON users(id)
WHERE is_active = true;
-- ==================== 注释和文档 ====================
COMMENT ON FUNCTION current_user_id() IS '获取当前认证用户的ID，从应用层设置的会话变量中读取';
COMMENT ON FUNCTION is_authenticated() IS '检查当前用户是否已认证';
COMMENT ON FUNCTION is_admin() IS '检查当前用户是否为管理员（预留功能）';
COMMENT ON POLICY "users_insert_registration" ON users IS '允许任何人注册新用户';
COMMENT ON POLICY "users_select_public" ON users IS '允许查看用户公开信息';
COMMENT ON POLICY "users_update_own" ON users IS '用户只能更新自己的信息';
COMMENT ON POLICY "users_delete_own" ON users IS '用户只能删除自己的账户';
COMMENT ON POLICY "products_select_public" ON products IS '允许任何人查看所有产品';
COMMENT ON POLICY "products_insert_authenticated" ON products IS '只有认证用户可以创建产品';
COMMENT ON POLICY "products_update_own" ON products IS '用户只能更新自己的产品';
COMMENT ON POLICY "products_delete_own" ON products IS '用户只能删除自己的产品';
-- 输出成功信息
DO $$ BEGIN RAISE NOTICE 'RLS policies have been successfully created for users and products tables';
RAISE NOTICE 'Security functions and audit system are now active';
RAISE NOTICE 'Remember to set app.current_user_id in your application sessions';
END $$;